﻿-- =============================================
-- Author:		周国强
-- Create date: 2014-5-20
-- Description:	统计交易单据，根据用户统计交易额
-- =============================================
CREATE PROCEDURE [dbo].[StatisticDealOrders] 
	-- Add the parameters for the stored procedure here
	@dateStart datetime, 
	@dateEnd datetime
AS
BEGIN
SELECT a.ID,a.Corporation,a.RealName,a.Address,a.Mobile,a.CType,a.Pinyin,a.SortIndex
,isnull(b.income,0) as income,isnull(b.expend,0) as expend,isnull(b.paid,0) as paid
,abs(isnull(b.income,0)-isnull(b.expend,0))-isnull(b.paid,0) as unpaid
,isnull(c.balance,0) as balance
FROM [invoicing2014].[dbo].[Customers] a
left join (
SELECT customerid,
sum(case when method in (1,4) then totalmoney else 0 end) as expend,
sum(case when method in (2,3) then totalmoney else 0 end) as income,
sum(paidmoney) as paid
FROM [invoicing2014].[dbo].[DealOrders]
where orderstatus<>99
and datediff(day,NoteDate,@dateStart)<=0
and datediff(day,NoteDate,@dateEnd)>=0
group by customerid
) b on a.id=b.customerid
left join (
select customerid,balance from 
[invoicing2014].[dbo].[PaidOrders]
where id in (
SELECT max(id)
FROM [invoicing2014].[dbo].[PaidOrders]
group by customerid)
) c on a.id=c.customerid
END